﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;

namespace AbacusCustom
{
    public partial class frmPrintReport : Form
    {
        public frmPrintReport()
        {
            InitializeComponent();
        }

        private void get_report_list()
        {
            Module db_conf = new Module();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
            "Data Source=" + db_conf.db_host + ";" +
            "Database=PSCUSTOM;" +
            "User ID=" + db_conf.db_user + ";" +
            "Password=" + db_conf.db_password + ";";
            conn.Open();

            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * FROM PS_REPORT WHERE ACCOUNT_ID='"+Module.session_userid+"' ORDER BY ID ASC";
            dgvReportSelection.Rows.Clear();
            SqlDataReader reader = cmd.ExecuteReader();
            
            while (reader.Read())
            {
                DataGridViewRow row = new DataGridViewRow();
                row.CreateCells(dgvReportSelection);
                row.Cells[0].Value = reader.GetValue(0).ToString();
                row.Cells[1].Value = reader.GetValue(2).ToString();
                row.Cells[2].Value = reader.GetValue(3).ToString();
                dgvReportSelection.Rows.Add(row);
            }

            conn.Close();
        }

        private void get_report_column(String report_id)
        {
            Module db_conf = new Module();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
            "Data Source=" + db_conf.db_host + ";" +
            "Database=PSCUSTOM;" +
            "User ID=" + db_conf.db_user + ";" +
            "Password=" + db_conf.db_password + ";";
            conn.Open();

            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT a.ID,REPORT_ID,c.NAME,d.NAME,b.NAME,a.NAME,a.DESCRIPTION FROM PS_REPORT_COLUMN a JOIN PS_COLUMN b ON a.COLUMN_ID=b.ID JOIN PS_TABLE_TYPE c ON a.TABLE_TYPE_ID = c.ID JOIN PS_TABLE d ON a.TABLE_ID = d.ID WHERE REPORT_ID='" + report_id + "' ORDER BY a.ID ASC";
            dgvColumnList.Rows.Clear();
            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                DataGridViewRow row = new DataGridViewRow();
                row.CreateCells(dgvColumnList);
                row.Cells[0].Value = reader.GetValue(0).ToString();
                row.Cells[1].Value = reader.GetValue(1).ToString();
                row.Cells[2].Value = reader.GetValue(2).ToString();
                row.Cells[3].Value = reader.GetValue(3).ToString();
                row.Cells[4].Value = reader.GetValue(4).ToString();
                row.Cells[5].Value = reader.GetValue(5).ToString();
                row.Cells[6].Value = reader.GetValue(6).ToString();
                dgvColumnList.Rows.Add(row);
            }

            conn.Close();
            
        }


        private void get_report_data(String sqlquery,String databasename,String columnlist,int numcolumn)
        {
            String hasil = "";
            Module db_conf = new Module();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
            "Data Source=" + db_conf.db_host + ";" +
            "Database="+databasename+";" +
            "User ID=" + db_conf.db_user + ";" +
            "Password=" + db_conf.db_password + ";";
            conn.Open();

            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sqlquery;
            
            SqlDataReader reader = cmd.ExecuteReader();

            hasil += columnlist;
            while (reader.Read())
            {
                for (int x = 0; x < numcolumn; x++)
                {
                    hasil += reader.GetValue(x).ToString().Trim()+";";
                }
                hasil += "\n";
            }

            using (StreamWriter writer = new StreamWriter("test.csv", false))
            {
                writer.Write(hasil);
            }

            conn.Close();
            
        }

        private void PrintReport_Load(object sender, EventArgs e)
        {
            get_report_list();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            String sqlquery = "SELECT ";
            String columnlist = "";

            String tableslist = "";
            String temptablename = "";
            String joinquery = "";
            String mainprimary = "";

            int x = 1;

            for (int a = 0; a < (dgvColumnList.RowCount - 1) - 1; a++)
            {
                if(a==0)
                {
                    sqlquery += dgvColumnList.Rows[a].Cells[4].Value.ToString().Trim();
                    columnlist += dgvColumnList.Rows[a].Cells[5].Value.ToString().Trim();
                }
                else{
                    sqlquery += "," + dgvColumnList.Rows[a].Cells[4].Value.ToString().Trim();
                    columnlist += "," + dgvColumnList.Rows[a].Cells[5].Value.ToString().Trim();
                }

                /*if (!temptablename.Equals(dgvColumnList.Rows[a].Cells[3].Value.ToString().Trim()) && !tableslist.Contains(dgvColumnList.Rows[a].Cells[3].Value.ToString().Trim()))
                {
                    MessageBox.Show(dgvColumnList.Rows[a].Cells[3].Value.ToString().Trim());
                    temptablename = dgvColumnList.Rows[a].Cells[3].Value.ToString().Trim();

                    if (x == 1)
                    {
                        joinquery = dgvColumnList.Rows[a].Cells[3].Value.ToString().Trim() + " t" + x.ToString();
                        mainprimary = dgvColumnList.Rows[a].Cells[7].Value.ToString().Trim();
                    }
                    else
                    {
                        joinquery += " JOIN " + dgvColumnList.Rows[a].Cells[3].Value.ToString().Trim() + " t" + x.ToString() + " ON t" + x.ToString() + "." + mainprimary + "= t1." + dgvColumnList.Rows[a].Cells[7].Value.ToString().Trim();
                    }
                    x++;
                }*/          
            }
            sqlquery +=" FROM "+joinquery;
            
            get_report_data(sqlquery, "TAIS",columnlist, (dgvColumnList.RowCount - 1)); 

        }

        private void dgvReportSelection_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                get_report_column(dgvReportSelection.Rows[e.RowIndex].Cells[0].Value.ToString());
            }
            catch (Exception)
            {

            }
        }
    }
}
